﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CFM.DTO;
using CFM.DAL;

namespace CFM.API
{
    public class ImportAPI
    {
        private DataHelper _helper = null;
        public ImportAPI()
        {
            _helper = new DataHelper();  
        }
        public List<Import> GetAll()
        {
            string sql = "select * from Import  order by ImportDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Import>(dr);
        }
        public List<ImportDetail> GetImportDetailByImportId(string importId)
        {
            string sql = "select * from ImportDetail where ImportId = @ImportId";
            SqlParameter[] prs = 
            {
                new SqlParameter("@ImportId",importId),
            };
            SqlDataReader dr = _helper.ExcuteDataReader(sql, prs);
            return _helper.MapDataReaderToObject<ImportDetail>(dr);
        }
        public bool CreateImport(Import import)
        {
            string sql = "insert into Import(ImportId,ProviderId,ProviderName,Total,ImportDate,UserId) values(@ImportId,@ProviderId,@ProviderName,@Total,@ImportDate,@UserId)";
            SqlParameter[] prs = 
            {
                new SqlParameter("@ImportId",import.ImportId),
                new SqlParameter("@ProviderId",import.ProviderId),
                new SqlParameter("@ProviderName",import.ProviderName),
                new SqlParameter("@Total",import.Total),
                new SqlParameter("@ImportDate",import.ImportDate),
                new SqlParameter("@UserId",import.UserId)
            };
            return _helper.ExcuteNonQuery(sql, prs);
        }
        public bool CreateImportDetail(ImportDetail importDetail)
        {
            string sql = "insert into ImportDetail(ImportId,ProductId,ProductName,UnitId,Quantity,Price,DetailTotal) values(@ImportId,@ProductId,@ProductName,@UnitId,@Quantity,@Price,@DetailTotal); ";
            sql += "update product set Quantity = (Quantity + @Quantity) where ProductId = @ProductId";
            SqlParameter[] prs = 
            {
                new SqlParameter("@ImportId",importDetail.ImportId),
                new SqlParameter("@ProductId",importDetail.ProductId),
                new SqlParameter("@ProductName",importDetail.ProductName),
                new SqlParameter("@UnitId",importDetail.UnitId),
                new SqlParameter("@Quantity",importDetail.Quantity),
                new SqlParameter("@Price",importDetail.Price),
                new SqlParameter("@DetailTotal",importDetail.DetailTotal)
            };
            return _helper.ExcuteNonQuery(sql, prs);
        }
        public List<Import> GetImportByAll(string keyword)
        {
            string sql = "select * from Import where ImportId like N'%" + keyword + "%' or ProviderName like N'%" + keyword + "%' or UserId like N'%" + keyword + "%' or ImportDate like '%" + keyword + "%'  order by ImportDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Import>(dr);
        }
        public List<Import> GetImportByProviderName(string providerName)
        {
            string sql = "select * from Import where ProviderName like N'%" + providerName + "%'  order by ImportDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Import>(dr);
        }
        public List<Import> GetImportByUerid(string userid)
        {
            string sql = "select * from Import where UserId like N'%" + userid + "%'  order by ImportDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Import>(dr);
        }
        public List<Import> GetImportByTolal(float total)
        {
            string sql = "select * from Import where Total = " + total;
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Import>(dr);
        }
        public List<Import> GetImportByDate(string keyword)
        {
            string sql = "select * from Import where ImportDate like '%" + keyword + "%'  order by ImportDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Import>(dr);
        }
        public List<Import> GetImportByImportId(string keyword)
        {
            string sql = "select * from Import where ImportId like N'%" + keyword + "%'  order by ImportDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Import>(dr);
        }
    }
}
